SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Crane,,Name>
-- Create date: <2011/11/13,,>
-- Description:	<Insert Order from ShoppingCart,,>
-- =============================================
CREATE PROCEDURE [dbo].[Insert_Order]
	(
	@UserId			uniqueidentifier,
	@ShipName		nvarchar(60),
	@ShipAddress	nvarchar(100),
	@ShipCity		nvarchar(15),
	@ShipPostalCode nvarchar(15),
	@ShipCountry	nvarchar(15),
	@Freight		smallmoney = 0,
	@OrderId		int	out
	)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	BEGIN TRAN
	
	INSERT INTO dbo.[Order](
		OrderDate, 
		ShipName,
		ShipAddress,
		ShipCity,
		ShipPostalCode,
		ShipCountry,
		UserId, 
		[Freight], 
		[Status]
		)
	VALUES(
		GETDATE(), 
		@ShipName,
		@ShipAddress,
		@ShipCity,
		@ShipPostalCode,
		@ShipCountry,
		@UserId, 
		@Freight, 
		'New')
	
	SELECT @OrderId = @@IDENTITY
	
	INSERT INTO dbo.OrderDetail(
		[OrderID]
		,[ProductID]
		,[UnitPrice]
		,[Quantity]
		,[Coupon]
		)
    SELECT  @OrderId
			,S.[ProductID]
			,S.[Quantity]
			,S.[UnitPrice]
			,S.[Coupon]
  FROM [dbo].[ShoppingCar] S
  WHERE S.UserId = @UserId
  
  --Clear ShoppingCar
  DELETE [dbo].[ShoppingCar]
  WHERE UserId = @UserId
  
  --Insert payment
  INSERT INTO dbo.[Payment]([OrderId]
           ,[AccountId]
           ,[Amount]
           ,[Status]
           ,[ProcessDate])
  SELECT @OrderId
      ,T.[AccountId]
      ,T.[Amount]
      ,''
      ,NULL
  FROM [dbo].[PaymentTmp] T
  WHERE T.UserId = @UserId    
  
  --Clear Payment Temp
  DELETE [dbo].[PaymentTmp]
  WHERE UserId = @UserId
  

  COMMIT
  
END

GO